package KatarinaBot;

import java.io.BufferedReader;
import java.sql.*;
import java.util.ArrayList;
import java.io.*;
import org.dtools.ini.*;

public class Database {

	/******************
	 * 
	 * Global variables
	 *
	 ******************/
	static ArrayList<String> tempStolenItems = new ArrayList<String>();
	String connectionName = "jdbc:sqlite:kbdata.db";
	
	/*********************
	 * 
	 *  Standard functions
	 *  
	 ********************/
	public static void main() throws Exception {

		// create an IniFile object
		IniFile ini = new BasicIniFile();
		
		// create an IniSection to hold personal data
		IniSection dataSection = ini.addSection("data");
		
		// create an IniItem for firstRun
		IniItem firstRun = dataSection.addItem("firstRun");
		
		// Set values
		IniSection getDataSection = ini.getSection("data");
		getDataSection.getItem("firstRun").setValue("true");
		
		String firstRunStatus = getDataSection.getItem("firstRun").getValue();
		
		if (firstRunStatus == "true") {
			Database db = new Database();
			db.restoreToDefaultStolenItemsDatabase();
			getDataSection.getItem("firstRun").setValue("false");
		}
		
		// Write from file
	}
	
	/*******************************
	 * 
	 * DATABASE FUNCTIONS FOR !steal
	 * 
	 *******************************/
	// Call to restore database to default values
	public void restoreToDefaultStolenItemsDatabase() throws Exception {
		
		// Declared variables
				String itemName;
				
				Class.forName("org.sqlite.JDBC");
				
				// Gets connection details
				Connection conn = DriverManager.getConnection(connectionName);
				
				// Setup database and table
				Statement stat = conn.createStatement();
				stat.executeUpdate("DROP TABLE IF EXISTS StolenItems");
				stat.executeUpdate("CREATE TABLE StolenItems (NameOfItem);");
				PreparedStatement prep = conn.prepareStatement("INSERT INTO StolenItems VALUES (?);");
			
				// Opens and scans the StolenItems.txt file
				BufferedReader textReader = new BufferedReader(
						new InputStreamReader(
								Database.class.getResourceAsStream("/KatarinaBot/StolenItems.txt")
								)
						);
				
				while ((itemName = textReader.readLine()) != null) {	
					prep.setString(1, itemName);
					prep.addBatch();
				}
				
				textReader.close();
				
				conn.setAutoCommit(false);
			    prep.executeBatch();
			    conn.setAutoCommit(true);
			
			    stolenItemsToArray(stat);
			    
			    conn.close(); // Closes the connection
			    
			    System.out.println("[INFO] Database restore complete!");
			    
	}

	// Sets default file to array
	public void stolenItemsToArray(Statement stat) throws Exception {
		
		int count = 0;
	
		// Deletes existing array
		tempStolenItems.clear();
		
		// Retrieves items from database and stores into temp array
	    ResultSet rs = stat.executeQuery("SELECT * FROM StolenItems;");
	    while (rs.next()) {
	      tempStolenItems.add(count, rs.getString("NameOfItem"));
	      count++;
	    }
	    rs.close(); // Closes the result set connection
		
	}
	
	// Displays list of stolen items
	public void displayStolenItems() throws Exception {
		
		int i = 0;
		
		System.out.println("***START LIST***");
		System.out.println("List of stolen items:");
		
		for (i = 0; i < tempStolenItems.size(); i++) {
			System.out.println(tempStolenItems.get(i));
		}
		
		System.out.println("***END LIST***");
		
	}
	
	// Inserts a new item to the database
	public void insertNewStolenItem(String itemName) throws Exception {
		
		Class.forName("org.sqlite.JDBC");
		
		// Gets connection details
		Connection conn = DriverManager.getConnection(connectionName);
		Statement stat = conn.createStatement();

		PreparedStatement prep = conn.prepareStatement("INSERT INTO StolenItems VALUES (?)");
		
		prep.setString(1, itemName);
		prep.addBatch();
		
		conn.setAutoCommit(false);
	    prep.executeBatch();
	    conn.setAutoCommit(true);
	    
	    stolenItemsToArray(stat);
	    
	    conn.close(); // Closes the connection
	    
	    System.out.println("[INFO] Item added to database.");
		
	}
	
	
	// Deletes an item from the database
	public void deleteStolenItem(String itemName) throws Exception {
		
		Class.forName("org.sqlite.JDBC");
		
		// Gets connection details
		Connection conn = DriverManager.getConnection(connectionName);
		Statement stat = conn.createStatement();

		PreparedStatement prep = conn.prepareStatement("DELETE FROM StolenItems WHERE NameOfitem=(?)");
		
		prep.setString(1, itemName);
		prep.addBatch();
		
		conn.setAutoCommit(false);
	    prep.executeBatch();
	    conn.setAutoCommit(true);
	    
	    stolenItemsToArray(stat);
	    
	    conn.close(); // Closes the connection
		
	    System.out.println("[INFO] Item has been deleted.");
	    
	}
	
	/***************************
	 * 
	 * DATABSE FUNCTIONS FOR !--
	 * 
	 ***************************/
}
